﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;

namespace CashierSystem.DoctorRecordManager
{
    class DoctorRecordManager:DbConnection
    {
        public static void saveTreatment(string customerId,string treatContent,string times,string doctorId)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            string sql = "insert into t_treatment(customerId,treatContent,doctorId,times,addDate,remainTimes) values('" + customerId + "','" + treatContent + "','";
            sql +=  doctorId + "','" + times + "','" + sd + "','" + times + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }

        }

        public static void loadOneCusTreat(ListView lv,string customerId)
        {
            string sql = "select id,treatContent,times,addDate,remainTimes from t_treatment where customerId = '" + customerId + "'";
            lv.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["treatContent"].ToString();
                    li.SubItems.Add(dbReader["times"].ToString());
                    string date = dbReader["addDate"].ToString();
                    DateTime d = DateTime.Parse(date);
                    string sd = d.ToString("yyyy-MM-dd");
                    li.SubItems.Add(sd);
                    li.SubItems.Add(dbReader["remainTimes"].ToString());
                    li.SubItems.Add(dbReader["id"].ToString());
                    lv.Items.Add(li);
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
 
        }

        public static void updateTreatment(string treatmentId)
        {
            string sql = "update t_treatment set remainTimes = remainTimes-1 where id = '" + treatmentId + "'";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static void saveOneTreat(string customerId, string treatId)
        {
            string date = DateTime.Now.ToString();
            DateTime d = DateTime.Parse(date);
            string sd = d.ToString("yyyy-MM-dd");
            string sql = "insert into t_doTreat(customerId,doDate,treatId) values('" + customerId + "','" + sd + "','" + treatId + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static void searchCusTreat(ListView lv, string customerId,string startDate,string endDate)
        {
            string sql = "select id,treatContent,times,addDate,remainTimes from t_treatment where customerId = '" + customerId + "' ";

            if (!startDate.Equals(""))
            {
                sql += " and addDate >= '" + startDate + "'";//convert(VARCHAR(10),
            }
            if (!endDate.Equals(""))
            {
                sql += " and addDate <= '" + endDate + "'";
            }

            lv.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["treatContent"].ToString();
                    li.SubItems.Add(dbReader["times"].ToString());
                    string date = dbReader["addDate"].ToString();
                    DateTime d = DateTime.Parse(date);
                    string sd = d.ToString("yyyy-MM-dd");
                    li.SubItems.Add(sd);
                    li.SubItems.Add(dbReader["remainTimes"].ToString());
                    li.SubItems.Add(dbReader["id"].ToString());
                    lv.Items.Add(li);
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }

        }

        public static void searchAllRecord(ListView lv,string startDate,string endDate)
        {
            string sql = "SELECT t_customer.customerName, t_customer.sex, t_treatment.treatContent,t_doTreat.doDate FROM t_doTreat ";
            sql += "INNER JOIN t_customer ON t_customer.customerId = t_doTreat.customerId INNER JOIN t_treatment ON t_treatment.id = t_doTreat.treatId ";
            if (!startDate.Equals(""))
            {
                sql += " and doDate >= '" + startDate + "'";//convert(VARCHAR(10),
            }
            if (!endDate.Equals(""))
            {
                sql += " and doDate <= '" + endDate + "'";
            }
            lv.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ListViewItem li = new ListViewItem();
                    li.SubItems.Clear();
                    li.SubItems[0].Text = dbReader["customerName"].ToString();
                    li.SubItems.Add(dbReader["sex"].ToString());
                    li.SubItems.Add(dbReader["treatContent"].ToString());
                    string date = dbReader["doDate"].ToString();
                    DateTime d = DateTime.Parse(date);
                    string sd = d.ToString("yyyy-MM-dd");
                    li.SubItems.Add(sd);
                    lv.Items.Add(li);
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }

        }
    }
}
